﻿<DataListSpec 
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes" 
	ID="62f4dc72-e4aa-4dae-9890-79f632d09e5a" 
	Name="PhonesForConstituent Data List"
	Description="Get Phone Numbers associated with a Constituent." 
	Author="Blackbaud Professional Services"
	common:SecurityUIFolder="Service Bus Adapter">


  <!-- define the SP that returns the data for the datalist -->
  <SPDataList SPName="USP_DATALIST_PHONESFORCONSTITUENT">
    <common:CreateProcedureSQL>
      <![CDATA[
create procedure dbo.USP_DATALIST_PHONESFORCONSTITUENT(
  @CONSTITUENTID uniqueidentifier)
as
	set nocount on;
  
SELECT
  p.ID,
	p.CONSTITUENTID,
	p.NUMBER,
	ptc.DESCRIPTION   as PHONETYPE,
	p.ISPRIMARY, 
	COALESCE(px.LOOKUPID, ''),
  p.DATEADDED,
  p.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME

  
FROM PHONE p
	LEFT OUTER JOIN PHONETYPECODE ptc ON p.PHONETYPECODEID  = ptc.ID
  LEFT JOIN USR_PHONE           px  ON p.ID               = px.ID
  LEFT OUTER JOIN CHANGEAGENT   ca  ON ca.ID              = p.CHANGEDBYID
WHERE p.CONSTITUENTID = @CONSTITUENTID
  
  

			]]>
    </common:CreateProcedureSQL>
  </SPDataList>

  <!-- describe any parameters (other than the context ID) defined on the SP -->
  <Parameters>
    <common:FormMetaData>
      <common:FormFields>
        <common:FormField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid"/>
      </common:FormFields>
    </common:FormMetaData>
  </Parameters>

  <!-- define the output fields in the list -->
  <Output>
    <OutputFields>
      <!-- 0 -->
      <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
      <!-- 1 -->
      <OutputField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid" />
      <!-- 2 -->
      <OutputField FieldID="NUMBER" Caption="Phone Number" DataType="String" />
      <!-- 3 -->
      <OutputField FieldID="PHONETYPE" Caption="Phone Type" DataType="String" />
      <!-- 4 -->
      <OutputField FieldID="ISPRIMARY" Caption="Is Primary" DataType="Boolean" />
      <!-- 5 -->
      <OutputField FieldID="LOOKUPID" Caption="Lookup ID" DataType="String" />
      <!-- 6 -->
      <OutputField FieldID="DATEADDED" Caption="Date Added" DataType="Date" />
      <!-- 7 -->
      <OutputField FieldID="DATECHANGED" Caption="Date Changed" DataType="Date" />
      <!-- 8 -->
      <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
    </OutputFields>
  </Output>
</DataListSpec>
